SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION [Live].[GetPropReason]
(
	-- Add the parameters for the function here
	@ExportId INT,
	@AssetIntrnlId INT 
)
RETURNS VARCHAR(150)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @ReturnVal VARCHAR(150)
	DECLARE @AssetType VARCHAR(50)

	SELECT @AssetType = (SELECT a.AssetType FROM FSFundModel.Reporting.Asset a WHERE a.ExportId = @ExportId AND a.AssetIntrnlId = @AssetIntrnlId)
	-- Add the T-SQL statements to compute the return value here
	SELECT 	@ReturnVal = CASE WHEN @AssetType = 'Instrument' THEN acts.[NBSReason] ELSE 'Proprietary' END	
		

	
	 FROM  FSFundModel.Reporting.Asset a 
	INNER JOIN  FSFundModel.Reporting.Instrument i 
	ON a.InstrumentId =i.InstrumentId 
	AND i.ExportId = a.ExportId 
	INNER JOIN  FSFundModel.Reporting.Trade t 
	ON t.ExportId = a.ExportId 
	AND t.InstrumentId = a.InstrumentId
	AND t.PortfolioId = a.PortfolioId
	AND t.LotId = a.LotId

	INNER JOIN  FSFundModel.Reporting.ActivitySummary acts 
	ON acts.ExportId = a.ExportId 
	AND acts.InstrumentId = a.InstrumentId 
	AND acts.ActivitySummaryId= t.ActivitySummaryId
	
	
	WHERE a.ExportId = @ExportId
	AND a.AssetIntrnlId= @AssetIntrnlId
	AND acts.ActivityType = 'Purchase'
	
	SET @ReturnVal = CASE WHEN @AssetType <> 'Instrument' THEN 'Proprietary' ELSE @ReturnVal END	
	
	-- Return the result of the function
	RETURN @ReturnVal

END		
GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Live', 'SCHEMA', N'Live', 'FUNCTION', N'GetPropReason', NULL, NULL
GO
